package com.sky.service.impl;

import com.sky.dto.*;
import com.sky.mapper.OrderMapper;
import com.sky.mapper.UserMapper;
import com.sky.service.ReportService;
import com.sky.service.WorkspaceService;
import com.sky.vo.*;
import io.swagger.v3.oas.models.security.SecurityScheme;
import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;

import java.io.InputStream;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

@Service
@Slf4j
public class ReportServiceImpl implements ReportService {
    @Autowired
    private RedisTemplate redisTemplate;
    @Autowired
    private OrderMapper orderMapper;
    @Autowired
    private UserMapper userMapper;
    @Autowired
    private WorkspaceService workspaceService;

    public TurnoverReportVO turnoverStatistics(LocalDate begin, LocalDate end) {
        //查redis，看从begin~end-1天数据是否存在
        LocalDate preEnd = end.plusDays(-1);
        String key="report:turnover:beforeEnd:"+begin+"_"+preEnd;
        String preEndStr = (String) redisTemplate.opsForValue().get(key);
        //支付、取消订单、拒单都需要维护当天的营业额
        String endKey="report:turnover:today_amount:"+end;
        //查redis，看end这一天的数据是否存在
        Double endAmount = (Double) redisTemplate.opsForValue().get(endKey);

        //dateList
        String dateListStr = getDateListStr(begin,end);
        //日期，以逗号分隔，例如：2022-10-01,2022-10-02,2022-10-03
        //营业额，以逗号分隔，例如：406.0,1520.0,75.0
        if(preEndStr!=null&&endAmount!=null) {
            return TurnoverReportVO.builder()
                    .dateList(dateListStr)
                    .turnoverList(preEndStr + "," + endAmount)
                    .build();
        }

        //可能某一天没有数据，那么这一天是空，需要设置为0
        //查数据库: 将order_time去除时分秒，再按order_time分组，然后限制order_time的范围
        List<TurnoverDateStatistic> tmpTurnoverDateStatistics =orderMapper.getAmountEveryDay(begin,end);
        List<TurnoverDateStatistic> turnoverDateStatisticList =new ArrayList<>();
        int pos_tmp=0;
        LocalDate tmpDate=begin;
        while (!tmpDate.equals(end.plusDays(1))) {
            TurnoverDateStatistic now = new TurnoverDateStatistic();
            if(pos_tmp<tmpTurnoverDateStatistics.size()){
                now=tmpTurnoverDateStatistics.get(pos_tmp);
            }
            if(tmpDate.equals(now.getDateTime())){
                turnoverDateStatisticList.add(now);
                pos_tmp++;
            }else{
                turnoverDateStatisticList.add(new TurnoverDateStatistic(tmpDate,0.0));
            }
            tmpDate = tmpDate.plusDays(1);
        }


        StringBuilder builder=new StringBuilder();
        //406.0,1520.0,75.0
        //将begin~end-1天的数据进行拼接
        for(int i = 0; i< turnoverDateStatisticList.size()-1; i++){
            builder.append(turnoverDateStatisticList.get(i).getStatistic().toString()+",");
        }
        if(builder.length()>0){
            builder.deleteCharAt(builder.length()-1);
        }

        //更新redis
        redisTemplate.opsForValue().set(key,builder.toString());
        redisTemplate.opsForValue().set(endKey, turnoverDateStatisticList.get(turnoverDateStatisticList.size()-1).getStatistic());

        builder.append(","+ turnoverDateStatisticList.get(turnoverDateStatisticList.size()-1).getStatistic().toString());

        return TurnoverReportVO.builder()
                .dateList(dateListStr)
                .turnoverList(builder.toString())
                .build();

    }

    private String getDateListStr(LocalDate begin, LocalDate end) {
        List<String> dateList=new ArrayList<>();
        LocalDate tmpDate = begin;
        while (!tmpDate.equals(end.plusDays(1))) {
            dateList.add(tmpDate.toString());
            tmpDate = tmpDate.plusDays(1);
        }
        return String.join(",", dateList);
    }


    public UserReportVO userStatistics(LocalDate begin, LocalDate end) {
        String dateListStr = getDateListStr(begin, end);
        String totalKey="report:user:total:"+begin+"_"+end;
        String newKey="report:user:new:"+begin+"_"+end;
        String totalUsersStr=(String) redisTemplate.opsForValue().get(totalKey);
        String newUsersStr=(String) redisTemplate.opsForValue().get(newKey);

        if(totalUsersStr!=null&&newUsersStr!=null) {
            return UserReportVO.builder()
                    .dateList(dateListStr)
                    .newUserList(newUsersStr)
                    .totalUserList(totalUsersStr)
                    .build();
        }

        List<UserDataStatistic> userData=userMapper.getNewUserEveryDay(begin,end);
        Integer totalUsers=userMapper.getUsersBeforeDate(LocalDateTime.of(end, LocalTime.MAX));

        //将没有数的那天设置为0
        StringBuilder newBuilder=new StringBuilder();
        StringBuilder totalBuilder=new StringBuilder();
        int posUserData=userData.size()-1;
        while (!end.equals(begin.plusDays(-1))) {
            totalBuilder.insert(0,","+totalUsers);
            //这一天有数据
            Integer todayNewUser=0;
            if(posUserData>=0&&userData.get(posUserData).getDateTime().equals(end)) {
                todayNewUser=userData.get(posUserData).getNewUsers();
                newBuilder.insert(0,","+todayNewUser);
                posUserData--;
            }else{
                newBuilder.insert(0,",0");
            }
            //当天的总用户-当天新用户=前一天总用户
            totalUsers=totalUsers-todayNewUser;
            end=end.plusDays(-1);
        }
        //去除开始的 ”,“
        totalUsersStr=totalBuilder.substring(1);
        newUsersStr=newBuilder.substring(1);

        //更新缓存
        redisTemplate.opsForValue().set(totalKey,totalUsersStr);
        redisTemplate.opsForValue().set(newKey,newUsersStr);

        return UserReportVO.builder()
                .dateList(dateListStr)
                .newUserList(newUsersStr)
                .totalUserList(totalUsersStr)
                .build();
    }

//    select date_format(order_time,'%Y-%m-%d')as dateTime,
//    count(if(status=5,1,null))as valid,
//    count(id) as total
//    from sky_take_out.orders
//    group by dateTime
//    having dateTime>='2024-11-01' and dateTime<='2024-11-30';
    public OrderReportVO ordersStatistics(LocalDate begin, LocalDate end) {
        String validOrderListKey="report:order:validList:"+begin+"_"+end;
        String totalOrderListKey="report:order:totalList:"+begin+"_"+end;
        String totalOrderCountKey="report:order:totalInt:"+begin+"_"+end;
        String validOrderCountKey= "report:order:validInt:" + begin + "_" + end;

        String dateListStr=getDateListStr(begin, end);
        //读redis
        String totalOrderList= (String) redisTemplate.opsForValue().get(totalOrderListKey);
        String validOrderCountList=(String) redisTemplate.opsForValue().get(validOrderListKey);
        Integer totalOrderCount=(Integer) redisTemplate.opsForValue().get(totalOrderCountKey);
        Integer validOrderCount=(Integer) redisTemplate.opsForValue().get(validOrderCountKey.toString());
        Double rate=0.0;
        if(totalOrderCount!=null&&totalOrderCount!=0){
            rate=validOrderCount.doubleValue()/totalOrderCount.doubleValue();
        }
        if(totalOrderList!=null&&validOrderCountList!=null
            &&totalOrderCount!=null&&validOrderCount!=null) {
            return OrderReportVO.builder()
                    .dateList(dateListStr)
                    .orderCountList(totalOrderList)
                    .validOrderCountList(validOrderCountList)
                    .totalOrderCount(totalOrderCount)
                    .validOrderCount(validOrderCount)
                    .orderCompletionRate(rate)
                    .build();
        }

        //读取数据库，只需要读出每日的订单总数和有效订单数即可
        List<OrderDataStatistic> orderDataList=orderMapper.getOrdersEveryDay(begin,end);

        StringBuilder validBuilder=new StringBuilder();
        StringBuilder totalBuilder=new StringBuilder();
        int pos=0;
        validOrderCount=0;
        totalOrderCount=0;
        //当天不存在数据则以0表示
        while (!begin.equals(end.plusDays(1))) {
            Integer tmpValid=0;
            Integer tmpTotal=0;
            if(pos<orderDataList.size()&&orderDataList.get(pos).getDateTime().equals(begin)) {
                tmpValid=orderDataList.get(pos).getValidOrders();
                tmpTotal=orderDataList.get(pos).getTotalOrders();
                pos++;
            }
            validOrderCount+=tmpValid;
            totalOrderCount+=tmpTotal;

            totalBuilder.append(tmpTotal+",");
            validBuilder.append(tmpValid+",");

            begin=begin.plusDays(1);
        }
        totalOrderList=totalBuilder.substring(0,totalBuilder.length()-1);
        validOrderCountList=validBuilder.substring(0,validBuilder.length()-1);


        //更新缓存
        redisTemplate.opsForValue().set(validOrderListKey,validOrderCountList);
        redisTemplate.opsForValue().set(validOrderCountKey,validOrderCount);
        redisTemplate.opsForValue().set(totalOrderListKey,totalOrderList);
        redisTemplate.opsForValue().set(totalOrderCountKey,totalOrderCount);

        if(totalOrderCount != 0){
            rate=validOrderCount.doubleValue()/totalOrderCount.doubleValue();
        }

        return OrderReportVO.builder()
                .dateList(dateListStr)
                .orderCountList(totalOrderList)
                .validOrderCountList(validOrderCountList)
                .totalOrderCount(totalOrderCount)
                .validOrderCount(validOrderCount)
                .orderCompletionRate(rate)
                .build();
    }

    public SalesTop10ReportVO getSalesTop10(LocalDate begin, LocalDate end) {
        String salesTop10NameKey="report:sales:top10:name:"+begin+"_"+end;
        String salesTop10NumberKey="report:sales:top10:number:"+begin+"_"+end;

        //查redis
        String nameList=(String) redisTemplate.opsForValue().get(salesTop10NameKey);
        String numberList=(String) redisTemplate.opsForValue().get(salesTop10NumberKey);

        if(nameList!=null&&numberList!=null) {
            return SalesTop10ReportVO.builder()
                    .nameList(nameList)
                    .numberList(numberList)
                    .build();
        }

        List<GoodsSalesDTO> top10=orderMapper.getSalesTop10(
                LocalDateTime.of(begin,LocalTime.MIN),LocalDateTime.of(end,LocalTime.MAX));

        nameList= StringUtils.join(
                top10.stream()
                        .map(GoodsSalesDTO::getName)
                        .collect(Collectors.toList()),
                ",");
        numberList=StringUtils.join(
                top10.stream()
                        .map(GoodsSalesDTO::getNumber)
                        .collect(Collectors.toList()),
                ",");

        //更新redis
        redisTemplate.opsForValue().set(salesTop10NameKey,nameList);
        redisTemplate.opsForValue().set(salesTop10NumberKey,numberList);


        return new SalesTop10ReportVO(nameList,numberList);

    }


    public void export(HttpServletResponse response) {
        LocalDate begin=LocalDate.now().minusDays(30);
        LocalDate end=LocalDate.now().minusDays(1);
        List<BusinessOrderData> businessEveryDay = orderMapper.getBusinessOrderDataIsEveryDay(LocalDateTime.of(begin,LocalTime.MIN), LocalDateTime.of(end,LocalTime.MAX), 1);
        List<UserDataStatistic> newUserEveryDay = userMapper.getNewUserEveryDay(begin, end);

        int posBusiness=0;
        int posUsers=0;

        InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("templates/运营数据报表模板.xlsx");
        Double turnoverSum=0.0;
        Integer newUsersSum=0;
        Integer validOrdersSum=0;
        Integer totalOrdersSum=0;

        try {
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet1 = xssfWorkbook.getSheet("Sheet1");

            int curRowNumber=7;
            while (!begin.equals(end.plusDays(1))) {

                Double turnover =0.0;
                Integer validOrders=0;
                Integer totalOrders=0;
                Integer newUsers=0;
                Double rate=0.0;
                Double averagePay=0.0;
                if(posBusiness<businessEveryDay.size()
                        &&businessEveryDay.get(posBusiness).getDateTime().equals(begin)) {
                    BusinessOrderData tmp = businessEveryDay.get(posBusiness);
                    turnover =tmp.getTurnover();
                    validOrders=tmp.getValidOrderCount();
                    totalOrders=tmp.getTotalOrders();
                    posBusiness++;
                }
                if(posUsers<newUserEveryDay.size()
                        &&newUserEveryDay.get(posUsers).getDateTime().equals(begin)) {
                    newUsers=newUserEveryDay.get(posUsers).getNewUsers();
                }
                if(!totalOrders.equals(0)){
                    rate=validOrders.doubleValue()/totalOrders.doubleValue();
                }
                if(!validOrders.equals(0)){
                    averagePay=turnover/totalOrders.doubleValue();
                }

                //写入表格
                XSSFRow row = sheet1.getRow(curRowNumber);
                curRowNumber++;
                row.getCell(1).setCellValue(begin.toString());
                row.getCell(2).setCellValue(turnover);
                row.getCell(3).setCellValue(validOrders);
                row.getCell(4).setCellValue(rate);
                row.getCell(5).setCellValue(averagePay);
                row.getCell(6).setCellValue(newUsers);

                newUsersSum+=newUsers;
                validOrdersSum+=validOrders;
                totalOrdersSum+=totalOrders;
                turnoverSum+=turnover;

                begin=begin.plusDays(1);
            }

            //写入时间
            sheet1.getRow(1).getCell(1)
                    .setCellValue("时间："+LocalDate.now().minusDays(30)+"至"+end);

            //写入概要数据
            XSSFRow row = sheet1.getRow(3);
            row.getCell(2).setCellValue(turnoverSum);
            row.getCell(4).setCellValue(totalOrdersSum==0?0.0:validOrdersSum.doubleValue()/totalOrdersSum);
            row.getCell(6).setCellValue(newUsersSum);

            row = sheet1.getRow(4);
            row.getCell(2).setCellValue(validOrdersSum);
            row.getCell(4).setCellValue(validOrdersSum==0?0.0:turnoverSum/validOrdersSum);


            //将这个数据输出到respond中
            ServletOutputStream outputStream = response.getOutputStream();
            xssfWorkbook.write(outputStream);
            outputStream.flush();

            outputStream.close();
            xssfWorkbook.close();
            inputStream.close();

        }catch (Exception e) {
            e.printStackTrace();
        }
    }
}
